﻿using Model;
using MySqlConnector;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class ActivityService
    {
        public bool AddActivity(Activity activity) 
        {
            string sql = "insert into activity values(@activityid,@activityname,@clubid,@activitydate,@place,@expenditure)";
            MySqlParameter[] para = new MySqlParameter[6];
            para[0] = new MySqlParameter("@activityid", activity.Activityid);
            para[1] = new MySqlParameter("@activityname", activity.Activityname);
            para[2] = new MySqlParameter("@clubid", activity.Clubid);
            para[3] = new MySqlParameter("activitydate",activity.Activitydate.ToShortDateString());
            para[4] = new MySqlParameter("@place", activity.Place);
            para[5] = new MySqlParameter("@expenditure", activity.Expenditure);
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public bool UpdateActivity(Activity activity) 
        {
            string sql = "update activity set activityid=@activityid,activityname=@activityname,clubid=@clubid,activitydate=@activitydate,place=@place,expenditure=@expenditure where activityname=@activityname";
            MySqlParameter[] para = new MySqlParameter[6];
            para[0] = new MySqlParameter("@activityid", activity.Activityid);
            para[1] = new MySqlParameter("@activityname", activity.Activityname);
            para[2] = new MySqlParameter("@clubid", activity.Clubid);
            para[3] = new MySqlParameter("activitydate", activity.Activitydate.ToShortDateString());
            para[4] = new MySqlParameter("@place", activity.Place);
            para[5] = new MySqlParameter("@expenditure", activity.Expenditure);
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public bool DeleteActivity(Activity activity)
        {
            string sql = "delete from activity where activityname=@activityname";
            MySqlParameter[] para = new MySqlParameter[1];
            para[0] = new MySqlParameter("@activityname", activity.Activityname);
            
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public ArrayList GetALLActivities(string sql)//查询社员信息
            {
                string ConnectionString = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
                MySqlConnection con = null;
                DataSet ds = new DataSet();
                ArrayList memberList = null;
                try
                {
                    con = new MySqlConnection(ConnectionString);
                    con.Open();
                    MySqlCommand cmd = new MySqlCommand(sql, con);
                    MySqlDataAdapter da = new MySqlDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(ds, "activity");
                    if (ds.Tables["activity"].Rows.Count > 0)
                    {
                        memberList = new ArrayList();
                        for (int i = 0; i < ds.Tables["activity"].Rows.Count; i++)
                        {
                            Activity activity = new Activity();
                            DataRow row = ds.Tables["activity"].Rows[i];
                            activity.Activityid = int.Parse(row["activityid"].ToString());
                            activity.Clubid = int.Parse(row["clubid"].ToString());
                            activity.Place = row["place"].ToString();
                            activity.Activitydate = Convert.ToDateTime(row["activitydate"]);
                            activity.Activityname = row["activityname"].ToString();
                            activity.Expenditure = float.Parse(row["expenditure"].ToString());
                            activity.Clubname = row["clubname"].ToString();

                            memberList.Add(activity);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    con.Close();
                }
                return memberList;
            }

        public ArrayList GetALLActivitiesByClubID(int clubid)//查询社员信息（用社团编号）
        {
            string sql = "select * from activity where clubid="+clubid;
            ArrayList arrayList = null;
            try
            {
                DataTable dt = SQLHelper.ExecuteQuery(sql);
                if (dt.Rows.Count > 0)
                {
                    arrayList = new ArrayList();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Activity activty = new Activity();
                        DataRow row = dt.Rows[i];
                        activty.Activityid = int.Parse(row["activityid"].ToString());
                        activty.Activityname = row["activityname"].ToString();
                        activty.Clubid = int.Parse(row["clubid"].ToString());
                        activty.Place = row["place"].ToString();
                        activty.Expenditure = int.Parse(row["expenditure"].ToString());
                        activty.Activitydate = Convert.ToDateTime(row["activitydate"]);
                        arrayList.Add(activty);
                    }
                }
            }catch (Exception ex) { throw new Exception(ex.Message); }
            return arrayList;
            }
        }
    }

